package common

import (
    "fmt"
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "time"
)

type DbLink struct{
    Username string
    Password string
    Network  string
    Server   string
    Port    int
    Database  string
}

type MysqlDbWorker struct{
	Dsn	     string
	Db 	     *sql.DB
    err   error
    Link  DbLink
}


func (dbw *MysqlDbWorker) PrintDsn() {
    fmt.Printf("%s\n",dbw.Dsn)
}


func MySQL_NewDbHandle(l DbLink) (*MysqlDbWorker){
    var err error
    dbw := &MysqlDbWorker{
        Dsn: fmt.Sprintf("%s:%s@%s(%s:%d)/%s", /*USERNAME,PASSWORD,NETWORK,SERVER,PORT,DATABASE*/ l.Username, l.Password, l.Network, l.Server, l.Port, l.Database)}
    dbw.Link = DbLink{
        /*Username: USERNAME, 
        Password: PASSWORD, 
        Network: NETWORK, 
        Server: SERVER, 
        Port: PORT, 
        Database: DATABASE */
        Username:  l.Username, 
        Password:  l.Password, 
        Network:   l.Network, 
        Server:    l.Server, 
        Port:      l.Port, 
        Database:  l.Database}
    dbw.Db,err = sql.Open("mysql", dbw.Dsn)
    if err != nil{
        fmt.Printf("Create database link failed,err:%v\n",err)
        return nil
    }
    return dbw
}

func (dbw *MysqlDbWorker) MySQL_DbConnProperty() {
    dbw.Db.SetConnMaxLifetime(100*time.Second)  //最大连接周期，超过时间的连接就close
    dbw.Db.SetMaxOpenConns(100)//设置最大连接数
    dbw.Db.SetMaxIdleConns(16)//设置闲置连接数
}

/*
 transaction control language
 atomicity,consistency,durability,isolation
 */
func (dbw *MysqlDbWorker) MySQL_TCL_Sample() string {
    //开启事务
    tcl, _ := dbw.Db.Begin()
    //id为1的price+1，id为2的price-1
    ret1, _ := tcl.Exec("update product set price = price + 1 where id = ?", 1)
    ret2, _ := tcl.Exec("update product set price = price - 1 where id = ?", 2)

    //如果id不存在,受影响行数则为0
    //接收影响行数，为0则失败
    rowCnt1, _ := ret1.RowsAffected()
    rowCnt2, _ := ret2.RowsAffected()

    //只有两条更新同时成功，那么才提交
    if rowCnt1 > 0 && rowCnt2 > 0 { 
        tcl.Commit() //提交事务
        return "Success"
    } else { //否则回滚
        tcl.Rollback() //回退事务
        return "Fail"
    }
}